
package DBLayer;
import ModelLayer.Customer;
import ModelLayer.Person;
import ModelLayer.Employee;
import ModelLayer.Order;
import java.sql.*;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBPerson implements IFDBPerson{
    
    private Connection con;
    PreparedStatement pStmtSelect;
    
    //creates a new instance of DBPerson
    public DBPerson()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    //get all customers
    @Override
    public ArrayList<Person> getAllCustomers()
    {
        return findPersonsWithPreQuery(null, "", false, false);
    }
    
    //get all employees
    @Override
    public ArrayList<Person> getAllEmployees()
    {
        return findPersonsWithPreQuery(null, "", false, true);
    }
    
    /**
     * The method finds a person (employee or customer)
     * @param id The id of the person to be found
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee True: The person is an employee false: a customer
     * @return ArrayList<Person> ArrayList of persons
     */
    @Override
    public ArrayList<Person> findPersonsId(int id , boolean retrieveAssociation, boolean isEmployee)
    { 
        String wClause = "";
        Person personObj = new Customer();
        
        if (isEmployee)
        {
            wClause = "  where e.employeeId = ";
            personObj = new Employee();
        }
        else
        {
            wClause = "  where c.customerId = ";
            personObj = new Customer();
        }
        return findPersonsWithPreQuery(id, wClause, retrieveAssociation, isEmployee);
    }   
    
    /**
     * The method finds all persons (employee or customer) matching a
     * specified first name
     * @param fName First name of the person to be found
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee True: The person is an employee false: a customer
     * @return ArrayList<Person> ArrayList of persons
     */
    @Override
    public ArrayList<Person> findPersonsfName(String fName, boolean retrieveAssociation, boolean isEmployee)
    {
        String wClause = "";
        Person personObj = null;
        
        if (isEmployee)
        {
            wClause = "  where e.fName = ";
            personObj = new Employee();
        }
        else
        {
            wClause = "  where c.fName = ";
            personObj = new Customer();
        }
        return findPersonsWithPreQuery(fName, wClause, retrieveAssociation, isEmployee);
    }    
    
    /**
     * The method finds all persons (employee or customer) matching a
     * specified last name
     * @param lName Last name of the person to be found
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee True: The person is an employee false: a customer
     * @return ArrayList<Person> ArrayList of persons
     */
    @Override
    public ArrayList<Person> findPersonslName(String lName, boolean retrieveAssociation, boolean isEmployee)
    {
        String wClause = "";
        Person personObj = null;
        
        if (isEmployee)
        {
            wClause = "  where e.lName = ";
            personObj = new Employee();
        }
        else
        {
            wClause = "  where c.lName = ";
            personObj = new Customer();
        }
        return findPersonsWithPreQuery(lName, wClause, false, isEmployee);
    }    
    
    /**
     * The method returns a person matching the specified id
     * @param personId The id of the person to be found
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee The person is an employee false: a customer
     * @return Person The person found
     */
    @Override
    public Person findPerson(int personId, boolean retrieveAssociation, boolean isEmployee)
    {   
        String wClause = "";
        Person personObj = null;
        
        if (isEmployee)
        {
            wClause = "  where e.employeeId = ";
            personObj = new Employee();
        }
        else
        {
            wClause = "  where c.customerId = ";
            personObj = new Customer();
        }
        
        personObj = findWithPreQuery(personId, wClause, retrieveAssociation, isEmployee);
        return personObj;
    }
    
    /**
     * The method inserts a person (employee or customer)
     * @param person The person to be inserted
     * @return int an int 1 if the method succedes and -1 if the method fails
     * @throws Exception 
     */
    @Override
    public int insertPerson(Person person) throws Exception
    {
        int nextPersonId = 0;
        int rc = -1;
        
        if(person instanceof Customer)
        {     
            Customer customer = (Customer) person;
            //call to get the next person id
            nextPersonId = GetMax.getMaxId("Select max(customerId) from Customer");
            nextPersonId = nextPersonId + 1;
            System.out.println("next customerId = " +  nextPersonId);
            
            PreparedStatement pstmt = null;
            String insertCustomer = "insert into Customer(customerId, fName, lName, address, phoneNo, mobileNo, zipCode, email)"+
                     "values (?,?,?,?,?,?,?,?)";
            System.out.println(insertCustomer);
            
            pstmt = con.prepareStatement(insertCustomer);
                
            pstmt.setInt(1,nextPersonId);
            pstmt.setString(2, customer.getfName());
            pstmt.setString(3, customer.getlName());
            pstmt.setString(4, customer.getAddress());
            pstmt.setString(5, customer.getPhoneNumber());
            pstmt.setString(6, customer.getMobileNumber());
            pstmt.setInt(7, customer.getZipCode());
            pstmt.setString(8, customer.getEmail());
                
            rc = pstmt.executeUpdate();       
        }
        if(person instanceof Employee)
        {
            Employee employee = (Employee) person;
            
            nextPersonId = GetMax.getMaxId("Select max(employeeId) from Employee");
            nextPersonId = nextPersonId + 1;
            System.out.println("next employeeId = " +  nextPersonId);
            
            PreparedStatement pstmt = null;
            String insertEmployee = "INSERT INTO Employee(employeeId, fName, lName, address, phoneNo, mobileNo, zipCode, email, accountInfo, comment, internalHourlyWage, externalHourlyWage, ssn, skill, typeNo, kreNo, cusNo)"+
                     "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            
            System.out.println(insertEmployee);
            
            pstmt = con.prepareStatement(insertEmployee);
                
            pstmt.setInt(1,nextPersonId);
            pstmt.setString(2, employee.getfName());
            pstmt.setString(3, employee.getlName());
            pstmt.setString(4, employee.getAddress());
            pstmt.setString(5, employee.getPhoneNumber());
            pstmt.setString(6, employee.getMobileNumber());
            pstmt.setInt(7, employee.getZipCode());
            pstmt.setString(8, employee.getEmail());
            pstmt.setString(9, employee.getAccountInfo());
            pstmt.setString(10, employee.getComment());
            pstmt.setDouble(11, employee.getInternalHourlyWage());
            pstmt.setDouble(12, employee.getExternalHourlyWage());
            pstmt.setLong(13, employee.getSsn());
            pstmt.setString(14, employee.getSkill());
            pstmt.setString(15, employee.getTypeNo());
            pstmt.setString(16, employee.getKreNo());
            pstmt.setString(17, employee.getCusNo());
                
            rc = pstmt.executeUpdate();    
        }
        return rc;
    }
    
    /**
     * The method updates information regarding a person (employee or customer)
     * @param person The person which information is to be updated
     * @return int an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int updatePerson(Person person)
    {
        int rc=-1;
        PreparedStatement pstmt = null;
        String query = "";
        if(person instanceof Customer)
        {
            Customer cusObj = (Customer) person;
            query = "UPDATE Customer SET "+
                          "fName = ?, "+
		 	  "lName = ?, "+
		 	  "address = ?, "+
                          "zipCode = ?, " +
                          "phoneNo = ?, " +
                          "mail = ?, " +
                          " WHERE personId = ?";
	System.out.println("Update query: " + query);
  		try
                { // update customer
                    pstmt = con.prepareStatement(query);
                    
                    pstmt.setString(1, cusObj.getfName());
                    pstmt.setString(2, cusObj.getlName());
                    pstmt.setString(3, cusObj.getAddress());
                    pstmt.setInt(4, cusObj.getZipCode());
                    pstmt.setString(5, cusObj.getPhoneNumber());
                    pstmt.setString(6, cusObj.getEmail());
                    pstmt.setInt(7, cusObj.getPersonId());
                   
                    rc = pstmt.executeUpdate();
                    pstmt.close();
		}//slut try
	 	catch(Exception ex)
                {
	 	 	System.out.println("Update exception in update customer db: "+ex);
                        System.out.println(ex.getMessage());
	  	}
        }
        if(person instanceof Employee)
        {
            Employee employee = (Employee) person;
            query = "UPDATE Employee SET "+
                          "fName = ?, "+
		 	  "lName = ?, "+
		 	  "address = ?, "+
                          "phoneNo = ?, "+
                          "mobileNo = ?, "+
                          "zipCode = ?, " +
                          "email = ?, " +
                          "accountInfo = ?, " +
                          "comment = ?, " +
                          "internalHourlyWage = ?, " +
                          "externalHourlyWaget = ?, " +
                          "ssn = ?, " +
                          "skill = ?, " +
                          "typeNo = ?, " +
                          "kreNo = ?, " +
                          "cusNo = ?, " +
                          " WHERE personId = ?";
            
            System.out.println("Update query: " + query);
            
            try
            {
                pstmt = con.prepareStatement(query);

                pstmt.setString(1, employee.getfName());
                pstmt.setString(2, employee.getlName());
                pstmt.setString(3, employee.getAddress());
                pstmt.setString(4, employee.getPhoneNumber());
                pstmt.setString(5, employee.getMobileNumber());
                pstmt.setInt(6, employee.getZipCode());
                pstmt.setString(7, employee.getEmail());
                pstmt.setString(8, employee.getAccountInfo());
                pstmt.setString(9, employee.getComment());
                pstmt.setDouble(10, employee.getInternalHourlyWage());
                pstmt.setDouble(11, employee.getExternalHourlyWage());
                pstmt.setLong(12, employee.getSsn());
                pstmt.setString(13, employee.getSkill());
                pstmt.setString(14, employee.getTypeNo());
                pstmt.setString(15, employee.getKreNo());
                pstmt.setString(16, employee.getCusNo());
                pstmt.setInt(17, employee.getPersonId());
                
                rc = pstmt.executeUpdate(); 
                pstmt.close();
            }    
            catch(Exception ex)
            {
	 	 System.out.println("Update exception in update employee db: "+ex);
                 System.out.println(ex.getMessage());
	    }
        }
        return rc;
    }
    
    /**
     * Method that deletes a person from the database
     * @param personId Person id of the person to be found
     * @param isEmployee True: The person is an employee false: a customer
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int deletePerson(int personId, boolean isEmployee)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "";
        
        if(isEmployee)
        {
            delete = "DELETE FROM Employee WHERE employeeId = ?";
        }
        else
        {
            delete = "DELETE FROM Customer WHERE customerId = ?";
        }
        
        System.out.println(delete);
        
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setInt(1,personId);
            rc = pstmt.executeUpdate(); 
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println("Error in deletePerson: "+e.getMessage());
        }
        return rc;
    }
    
    /**
     * Method to build an employee or customer object
     * @param results Resultset of information related to a person
     * @param isEmployee True: The person is an employee false: a customer
     * @return Person The person object that has been build
     */
    private Person buildPerson(ResultSet results, boolean isEmployee)
    {   
          Person personObj = null;
  
          try
          {
                String fName = results.getString("fName");
                String lName = results.getString("lName");
                String address = results.getString("address");
                String phoneNo = results.getString("phoneNo");
                String mobileNo = results.getString("mobileNo");
	  	String email = results.getString("email");
	  	int zipCode = results.getInt("zipCode");
                String city = results.getString("city");
                
                if(isEmployee)
                {
                    int employeeId = results.getInt("employeeId");
                    String accountInfo = results.getString("accountInfo");
                    String comment = results.getString("comment");
                    double internalHourlyWage = results.getDouble("internalHourlyWage");
                    double externalHourlyWage = results.getDouble("externalHourlyWage");
                    long ssn = results.getInt("ssn");
                    String skill = results.getString("skill");
                    String typeNo = results.getString("typeNo");
                    String debNo = results.getString("kreNo");
                    String cusNo = results.getString("cusNo");
    
                    personObj = new Employee(employeeId, fName, lName, address, zipCode, city, phoneNo, mobileNo, email, accountInfo, comment, internalHourlyWage, externalHourlyWage, ssn, skill, typeNo, debNo, cusNo);
                }
                else 
                {
                    int customerId = results.getInt("customerId");
                    personObj = new Customer(customerId, fName, lName, address, zipCode, city, phoneNo, mobileNo, email);
                }           
          }
          catch(Exception e)
          {
             System.out.println("error in building the person object");
             System.out.println(e.getMessage());
          }
          return personObj;
      }
    
    /**
     * The method executes a prepared statement
     * @param value The value to be used in the prepared statement (personId 
     * or name of person)
     * @param wClause Where clause in SQL statement
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee True: The person is an employee false: a customer
     * @return Person A person object
     */
    private Person findWithPreQuery(int value, String wClause, boolean retrieveAssociation, boolean isEmployee)
        {   System.out.println("Find with preCompiled");
            
            ArrayList<Order> orders = null;
            int cusId = 0;
            String cusName = "";
            
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStmt(wClause, isEmployee);   
            }
            Person personObj = null;
            ResultSet results;
            try
            {   
                cusId = value;
                pStmtSelect.setInt(1,cusId);
                    
                results = pStmtSelect.executeQuery();
                System.out.println(results);
                if(results.next())
                {
                    personObj = buildPerson(results, isEmployee);
                    pStmtSelect.close();
                    if(retrieveAssociation)
                    {   //The ArrayList of orders is to be build as well
                        IFDBOrder dbOrderObj = new DBOrder();
                        if(personObj instanceof Customer)
                        {
                            Customer customer = (Customer) personObj;
                            orders = dbOrderObj.findOrders(customer.getPersonId(), true);
                        }
                        else if(personObj instanceof Employee)
                        {
                            Employee employee = (Employee) personObj;
                            orders = dbOrderObj.findOrders(employee.getPersonId(), true);
                        }
                        System.out.println("Orders is selected");
                        personObj.setOrderArray(orders);
                    }       
                }
                else
                {
                    personObj = null;
                }
            }
            catch(Exception e)
            {
                System.out.println("Error in findWithPreQuery "+e.getMessage());
            }
            pStmtSelect = null;
            return personObj;
       }
    
    /**
     * The method executes a prepared statement
     * @param value The value to be used in the prepared statement (personId 
     * or name of person)
     * @param wClause Where clause in SQL statement
     * @param retrieveAssociation Whether or not to build associations
     * @param isEmployee True: The person is an employee false: a customer
     * @return ArrayList<Person> An arrayList of persons
     */
    private ArrayList<Person> findPersonsWithPreQuery(Object value, String wClause, boolean retrieveAssociation, boolean isEmployee)
        {   System.out.println("Find with preCompiled");
            
            ArrayList<Order> orders = null;
            int cusId = 0;
            String cusName = "";
            
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStmt(wClause, isEmployee);   
            }
            ArrayList<Person> list = new ArrayList<Person>();
            Person personObj = null;
            ResultSet results;
            try
            {   
                //if(!wClause.equals(""))
                if(value instanceof Integer && !wClause.equals(""))
                {
                    cusId = (Integer) value;
                    pStmtSelect.setInt(1,cusId);
                }
                if(value instanceof String && !wClause.equals(""))
                {
                    cusName = (String) value;
                    pStmtSelect.setString(1,cusName);
                }
                results = pStmtSelect.executeQuery();
                System.out.println(results);
          
                while( results.next() )
                {
                    personObj = buildPerson(results, isEmployee);	
                    list.add(personObj);
                }//end while 
                pStmtSelect.close();
                    if(retrieveAssociation)
                    {   //The ArrayList of orders is to be build as well
                        
                        for(Person perObj : list)
                        { 
                            IFDBOrder dbOrderObj = new DBOrder();
                            if(perObj instanceof Customer)
                            {
                                Customer customer = (Customer) perObj;
                                orders = dbOrderObj.findOrders(customer.getPersonId(), true);
                            }
                            if(perObj instanceof Employee)
                            {
                                Employee employee = (Employee) perObj;
                                orders = dbOrderObj.findOrders(employee.getPersonId(), true);
                            }
                    
                            System.out.println("Orders is selected");
                            perObj.setOrderArray(orders);
                        }
                    }    
                else
                {
                    personObj = null;
                }
            }
            catch(Exception e)
            {
                System.out.println("Error in findPersonsWithPreQuery "+ e.getMessage());
            }
            pStmtSelect = null;
            return list;
       }
      
    /**
     * The method builds the prepared statement
     * @param wClause Where clause in SQL statement
     * @param isEmployee True: The person is an employee false: a customer
     * @return PreparedStatement The prepared statement that has been build
     */ 
      private PreparedStatement getPreparedStmt(String wClause, boolean isEmployee)
        {
           String query = "";
           try
           {  
                if(isEmployee && !wClause.equals(""))
                {
                    query = "SELECT * FROM Employee as e join ZipCode as p on e.zipCode = p.zipCode" +
                        wClause + " ?";
                }
                else if(isEmployee && wClause.equals(""))
                {
                    query = "SELECT * FROM Employee as e join ZipCode as p on e.zipCode = p.zipCode";
                }
                if(!isEmployee && !wClause.equals(""))
                {
                    query = "SELECT * FROM Customer as c join ZipCode as p on c.zipCode = p.zipCode" +
                        wClause + " ?";
                }
                else if(!isEmployee && wClause.equals(""))
                {
                    query = "SELECT * FROM Customer as c join ZipCode as p on c.zipCode = p.zipCode";
                }
                System.out.println("Preparestatement " + query);
                pStmtSelect = con.prepareStatement(query);
           }
           catch(Exception e)
           {
                System.out.println(e.getMessage());
           }
           return pStmtSelect;
        }   
}